package dao.mysql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

import model.LeaveDTO;
import model.LeaveSearchDTO;
import dao.DAOException;
import dao.LeaveDAO;

public class LeaveDAOImpl implements LeaveDAO {
	private static final String dbUrl = "jdbc:mysql://localhost:3306/leaveappforiss";
	private static final String dbUserName = "root";
	private static final String dbPassword = "password";

	public void deleteLeave(LeaveDTO leave) throws DAOException {
		String deleteSql = "DELETE FROM leaves WHERE LeaveID="
				+ leave.getLeaveID() + ";";
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing delete: " + deleteSql);
			int rows = st.executeUpdate(deleteSql);
			st.close();
		} catch (Exception e) {
			String error = "Error deleting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	public ArrayList<LeaveDTO> findAllLeave() throws DAOException {
		ArrayList<LeaveDTO> items = new ArrayList<LeaveDTO>();
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		String selectSql = "SELECT * FROM leaves;";
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectSql);
			ResultSet rs = st.executeQuery(selectSql);
			while (rs.next()) {
				LeaveDTO oneLeave = new LeaveDTO();
				oneLeave.setEmployeeID(rs.getInt("EmployeeID"));
				oneLeave.setLeaveTypeID(rs.getInt("LeaveTypeID"));
				oneLeave.setStartDate(rs.getDate("StartDate"));
				oneLeave.setEndDate(rs.getDate("EndDate"));
				oneLeave.setLeavePeriod(rs.getDouble("LeavePeriod"));
				oneLeave.setReason(rs.getString("Reason"));
				oneLeave.setWorkdissmenition(rs.getInt("WorkDissmenition"));
				oneLeave.setContactDetail(rs.getString("ContactDetail"));
				oneLeave.setStatus(rs.getString("Status"));
				oneLeave.setComment(rs.getString("Comment"));
				oneLeave.setCreateBy(rs.getInt("CreateBy"));
				oneLeave.setCreateDate(rs.getDate("CreateDate"));
				oneLeave.setModifiedBy(rs.getInt("ModifiedBy"));
				oneLeave.setModifiedDate(rs.getDate("ModifiedDate"));
				
				items.add(oneLeave);
			}
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		return items;
	}
	public ArrayList<LeaveDTO> findLeaveHistory(int employeeID) throws DAOException {
		ArrayList<LeaveDTO> aa = new ArrayList<LeaveDTO>();
		String selectSql = null;
		 selectSql = "Select l.leaveid,l.employeeid,e.firstname, e.surname, l.leavetypeid,l.StartDate,l.EndDate,l.leaveperiod,l.Reason,l.workdissmenition, l.contactdetail,l.status,l.comment,l.CreateBy,l.createdate,l.modifiedby,l.modifieddate from leaves as l , employee as e where l.employeeid= e.employeeid and l.status='approved' and e.employeeid='"+ employeeID + "';";
		//selectSql = "Select l.*,e.* where l.employeeid= e.employeeid AND e.employeeid='"+ employeeID + "';";
		//selectSql="Select * from leaves where employeeid='"+ employeeID + "';";
		return loadLeaveData(aa, selectSql);
	}

	public ArrayList<LeaveDTO> findLeaveforEmployee(int employeeID) throws DAOException {
		ArrayList<LeaveDTO> aa = new ArrayList<LeaveDTO>();
		String selectSql = null;
		 selectSql = "Select l.leaveid,l.employeeid,e.firstname, e.surname, l.leavetypeid,l.StartDate,l.EndDate,l.leaveperiod,l.Reason,l.workdissmenition, l.contactdetail,l.status,l.comment,l.CreateBy,l.createdate,l.modifiedby,l.modifieddate from leaves as l , employee as e where l.employeeid= e.employeeid  and l.status ='applied' and e.employeeid='"+ employeeID + "';";
		//selectSql = "Select l.*,e.* where l.employeeid= e.employeeid AND e.employeeid='"+ employeeID + "';";
		//selectSql="Select * from leaves where employeeid='"+ employeeID + "';";
		return loadLeaveData(aa, selectSql);
	}
	private ArrayList<LeaveDTO> loadLeaveData(ArrayList<LeaveDTO> aa,
			String selectSql) throws DAOException {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		Connection conn = null;
		
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectSql);
			ResultSet rs = st.executeQuery(selectSql);
			while (rs.next()) {
				LeaveDTO oneLeave = new LeaveDTO();
				oneLeave.setLeaveID(rs.getInt("leaveID"));
				oneLeave.setEmployeeID(rs.getInt("employeeID"));
				oneLeave.setFirstName(rs.getString("firstName"));
				oneLeave.setSurName(rs.getString("surName"));
				oneLeave.setLeaveTypeID(rs.getInt("leaveTypeID"));
				oneLeave.setStartDate(rs.getDate("startDate"));
				oneLeave.setEndDate(rs.getDate("endDate"));
				oneLeave.setLeavePeriod(rs.getDouble("leavePeriod"));
				oneLeave.setReason(rs.getString("reason"));
				oneLeave.setWorkdissmenition(rs.getInt("workdissmenition"));
				oneLeave.setContactDetail(rs.getString("contactDetail"));
				oneLeave.setStatus(rs.getString("status"));
				oneLeave.setComment(rs.getString("comment"));
				oneLeave.setCreateBy(rs.getInt("createBy"));
				oneLeave.setCreateDate(rs.getDate("createDate"));
				oneLeave.setModifiedBy(rs.getInt("modifiedBy"));
				
				oneLeave.setModifiedDate(rs.getDate("modifiedDate"));
				System.out.println(oneLeave.toString());
				aa.add(oneLeave);
			
			}
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		System.out.println(aa.toString());
		return aa;
	}
	/*public LeaveDTO findLeave(int employeeID) throws DAOException {
		String selectSql = "SELECT * FROM leaves WHERE EmployeeID=" + employeeID
				+ ";";
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		LeaveDTO oneLeave = new LeaveDTO();
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectSql);
			ResultSet rs = st.executeQuery(selectSql);
			while (rs.next()) {
				oneLeave.setEmployeeID(rs.getInt("EmployeeID"));
				oneLeave.setLeaveTypeID(rs.getInt("LeaveTypeID"));
				oneLeave.setStartDate(rs.getDate("StartDate"));
				oneLeave.setEndDate(rs.getDate("EndDate"));
				oneLeave.setLeavePeriod(rs.getDouble("LeavePeriod"));
				oneLeave.setReason(rs.getString("Reason"));
				oneLeave.setWorkDissmenition(rs.getInt("WorkDissmenition"));
				oneLeave.setContactDetail(rs.getString("ContactDetail"));
				oneLeave.setStatus(rs.getString("Status"));
				oneLeave.setComment(rs.getString("Comment"));
				oneLeave.setCreateBy(rs.getInt("CreateBy"));
				oneLeave.setCreateDate(rs.getDate("CreateDate"));
				oneLeave.setModifiedBy(rs.getInt("ModifiedBy"));
				oneLeave.setModifiedDate(rs.getDate("ModifiedDate"));
			
			}
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		System.out.println(oneLeave.toString());
		return oneLeave;
	}*/

	public void insertLeave(LeaveDTO leave) throws DAOException, SQLException {
		String insertSql = "INSERT INTO leaves VALUES(" + getNewLeaveID() + leave.getEmployeeID()
				+ ", " + leave.getLeaveTypeID() + ", '" + leave.getStartDate()
				+ "', '" + leave.getEndDate()+ "', '" + leave.getLeavePeriod()
				+ "', '" + leave.getReason()+ "', '" + leave.getWorkdissmenition()
				+ "', '" + leave.getContactDetail()+ "', '" + leave.getStatus()
				+ "', '" + leave.getComment()+ "', '" + leave.getCreateBy()
				+ "', '" + leave.getCreateDate()+ "', '" + leave.getModifiedBy()
				+ "', '" + leave.getModifiedDate()+ "');";
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing insert: " + insertSql);
			int res = st.executeUpdate(insertSql);
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
	}
	private int getNewLeaveID() throws SQLException{
		int id = countAllLeaves();
		id ++;
		return id;
	}

	public int countAllLeaves() throws SQLException {

	    String sql = "SELECT count(*) FROM leaves";
	    PreparedStatement stmt = null;
	    ResultSet result = null;
	    int allRows = 0;

	    try {
	    	Connection conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
	        stmt = conn.prepareStatement(sql);
	        result = stmt.executeQuery();

	        if (result.next())
	            allRows = result.getInt(1);
	    } finally {
	        if (result != null)
	            result.close();
	        if (stmt != null)
	            stmt.close();
	    }
	    return allRows;
	}

	public void updateLeave(LeaveDTO leave) throws DAOException {
		String updateSql = "UPDATE leaves SET EmployeeID = "
				+ leave.getEmployeeID() + ", LeaveTypeID = " + leave.getLeaveTypeID()
				+ ", StartDate = '" + leave.getStartDate()
				+ "', EndDate = '" + leave.getEndDate()+ "', LeavePeriod = " + leave.getLeavePeriod()
				+ ", Reason = '" + leave.getReason()+ "', WorkDissmenition = " + leave.getWorkdissmenition()
				+ ", ContactDetail = '" + leave.getContactDetail()+ "', Status = '" + leave.getStatus()
				+ "', Comment = '" + leave.getComment()+ "', CreateBy = " + leave.getCreateBy()
				+ ", CreateDate = '" + leave.getCreateDate()+ "', ModifiedBy = " + leave.getModifiedBy()
				+ ", ModifiedDate = '" + leave.getModifiedDate()
				+ "' WHERE LeaveID=" + leave.getLeaveID() + ";";
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing update: " + updateSql);
			int res = st.executeUpdate(updateSql);
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}

	}

	public ArrayList<LeaveDTO> findLeaveByCriteria(LeaveSearchDTO so)
			throws DAOException {
		ArrayList<LeaveDTO> list = new ArrayList<LeaveDTO>();
		String selectSql = null;
		if (so.getEmployeeName().trim().equalsIgnoreCase("")) {

			selectSql = "SELECT * FROM leaves WHERE EmployeeID LIKE "
					+ so.getEmployeeID() + "%;";
		} else {
			if (so.getEmployeeID()==0) {
				selectSql = "SELECT * FROM leaves WHERE EmployeeName LIKE '"
						+ so.getEmployeeName().trim() + "%';";
			} else {
				selectSql = "SELECT * FROM leaves WHERE EmployeeID LIKE "
						+ so.getEmployeeID() + "% AND EmployeeName LIKE '"
						+ so.getEmployeeName() + "%';";
			}
		}
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectSql);
			ResultSet rs = st.executeQuery(selectSql);
			while (rs.next()) {
				LeaveDTO oneLeave = new LeaveDTO();
				oneLeave.setEmployeeID(rs.getInt("EmployeeID"));
				oneLeave.setLeaveTypeID(rs.getInt("LeaveTypeID"));
				oneLeave.setStartDate(rs.getDate("StartDate"));
				oneLeave.setEndDate(rs.getDate("EndDate"));
				oneLeave.setLeavePeriod(rs.getDouble("LeavePeriod"));
				oneLeave.setReason(rs.getString("Reason"));
				oneLeave.setWorkdissmenition(rs.getInt("WorkDissmenition"));
				oneLeave.setContactDetail(rs.getString("ContactDetail"));
				oneLeave.setStatus(rs.getString("Status"));
				oneLeave.setComment(rs.getString("Comment"));
				oneLeave.setCreateBy(rs.getInt("CreateBy"));
				oneLeave.setCreateDate(rs.getDate("CreateDate"));
				oneLeave.setModifiedBy(rs.getInt("ModifiedBy"));
				oneLeave.setModifiedDate(rs.getDate("ModifiedDate"));
				list.add(oneLeave);
			}
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		
		return list;
	}
}
